"""
数据库连接和操作模块
负责SQLite数据库的连接管理和数据访问
"""

import sqlite3
import os
from datetime import datetime
from typing import List, Optional
from backend.models.alarm import Alarm, AlarmCreate

# 数据库文件路径
DB_PATH = os.path.join(os.path.dirname(__file__), "..", "..", "data", "database", "alarms.db")

def init_db():
    """初始化数据库"""
    # 确保目录存在
    os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
    
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    # 创建告警表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS alarms (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            global_alarm_id TEXT NOT NULL UNIQUE,
            alarm_status TEXT NOT NULL,
            alarm_title TEXT NOT NULL,
            alarm_type TEXT NOT NULL,
            alarm_level TEXT NOT NULL,
            occurrence_time TEXT NOT NULL,
            clear_time TEXT,
            region TEXT NOT NULL,
            datacenter TEXT NOT NULL,
            device_name TEXT NOT NULL,
            device_type TEXT NOT NULL,
            device_vendor TEXT NOT NULL,
            dispatch_status TEXT NOT NULL,
            resource_pool TEXT NOT NULL,
            alarm_content TEXT NOT NULL,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP,
            updated_at TEXT DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    # 创建索引以提高查询性能
    cursor.execute('''
        CREATE INDEX IF NOT EXISTS idx_global_alarm_id ON alarms(global_alarm_id)
    ''')
    
    cursor.execute('''
        CREATE INDEX IF NOT EXISTS idx_occurrence_time ON alarms(occurrence_time)
    ''')
    
    cursor.execute('''
        CREATE INDEX IF NOT EXISTS idx_alarm_level ON alarms(alarm_level)
    ''')
    
    cursor.execute('''
        CREATE INDEX IF NOT EXISTS idx_alarm_status ON alarms(alarm_status)
    ''')
    
    cursor.execute('''
        CREATE INDEX IF NOT EXISTS idx_region ON alarms(region)
    ''')
    
    cursor.execute('''
        CREATE INDEX IF NOT EXISTS idx_datacenter ON alarms(datacenter)
    ''')
    
    cursor.execute('''
        CREATE INDEX IF NOT EXISTS idx_dispatch_status ON alarms(dispatch_status)
    ''')
    
    conn.commit()
    conn.close()

def insert_alarm(alarm: AlarmCreate):
    """插入告警记录"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    try:
        cursor.execute('''INSERT INTO alarms (
            global_alarm_id, alarm_status, alarm_title, alarm_type,
            alarm_level, occurrence_time, clear_time, region,
            datacenter, device_name, device_type, device_vendor,
            dispatch_status, resource_pool, alarm_content
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', (
            alarm.global_alarm_id,
            alarm.alarm_status,
            alarm.alarm_title,
            alarm.alarm_type,
            alarm.alarm_level,
            alarm.occurrence_time.isoformat(),
            alarm.clear_time.isoformat() if alarm.clear_time else None,
            alarm.region,
            alarm.datacenter,
            alarm.device_name,
            alarm.device_type,
            alarm.device_vendor,
            alarm.dispatch_status,
            alarm.resource_pool,
            alarm.alarm_content
        ))
        
        conn.commit()
        print(f"✅ 成功插入告警: {alarm.global_alarm_id}")
    except sqlite3.IntegrityError as e:
        conn.rollback()
        print(f"⚠️ 告警已存在，跳过: {alarm.global_alarm_id} - {str(e)}")
        raise  # 重新抛出异常，让上层知道是重复数据
    except Exception as e:
        conn.rollback()
        print(f"❌ 插入告警失败: {alarm.global_alarm_id} - {str(e)}")
        raise
    finally:
        conn.close()

def get_alarm_by_id(alarm_id: str):
    """根据全局告警ID获取告警记录"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    cursor.execute('''
        SELECT * FROM alarms WHERE global_alarm_id = ?
    ''', (alarm_id,))
    
    row = cursor.fetchone()
    conn.close()
    
    if row:
        return Alarm(
            id=row[0],
            global_alarm_id=row[1],
            alarm_status=row[2],
            alarm_title=row[3],
            alarm_type=row[4],
            alarm_level=row[5],
            occurrence_time=datetime.fromisoformat(row[6]),
            clear_time=datetime.fromisoformat(row[7]) if row[7] else None,
            region=row[8],
            datacenter=row[9],
            device_name=row[10],
            device_type=row[11],
            device_vendor=row[12],
            dispatch_status=row[13],
            resource_pool=row[14],
            alarm_content=row[15]
        )
    
    return None

def get_alarms(skip: int = 0, limit: int = 100):
    """获取告警记录列表"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    cursor.execute('''
        SELECT * FROM alarms ORDER BY occurrence_time DESC LIMIT ? OFFSET ?
    ''', (limit, skip))
    
    rows = cursor.fetchall()
    conn.close()
    
    alarms = []
    for row in rows:
        alarms.append(Alarm(
            id=row[0],
            global_alarm_id=row[1],
            alarm_status=row[2],
            alarm_title=row[3],
            alarm_type=row[4],
            alarm_level=row[5],
            occurrence_time=datetime.fromisoformat(row[6]),
            clear_time=datetime.fromisoformat(row[7]) if row[7] else None,
            region=row[8],
            datacenter=row[9],
            device_name=row[10],
            device_type=row[11],
            device_vendor=row[12],
            dispatch_status=row[13],
            resource_pool=row[14],
            alarm_content=row[15]
        ))
    
    return alarms

def query_alarms(
    global_alarm_id: Optional[str] = None,
    alarm_status: Optional[str] = None,
    alarm_title: Optional[str] = None,
    alarm_type: Optional[str] = None,
    alarm_level: Optional[str] = None,
    region: Optional[str] = None,
    datacenter: Optional[str] = None,
    device_name: Optional[str] = None,
    device_type: Optional[str] = None,
    device_vendor: Optional[str] = None,
    dispatch_status: Optional[str] = None,
    resource_pool: Optional[str] = None,
    start_time: Optional[str] = None,
    end_time: Optional[str] = None,
    skip: int = 0,
    limit: int = 100
):
    """根据条件查询告警记录"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    # 构建查询语句
    query = "SELECT * FROM alarms WHERE 1=1"
    params = []
    
    if global_alarm_id:
        query += " AND global_alarm_id LIKE ?"
        params.append(f"%{global_alarm_id}%")
    
    if alarm_status:
        query += " AND alarm_status = ?"
        params.append(alarm_status)
    
    if alarm_title:
        query += " AND alarm_title LIKE ?"
        params.append(f"%{alarm_title}%")
    
    if alarm_type:
        query += " AND alarm_type = ?"
        params.append(alarm_type)
    
    if alarm_level:
        query += " AND alarm_level = ?"
        params.append(alarm_level)
    
    if region:
        query += " AND region = ?"
        params.append(region)
    
    if datacenter:
        query += " AND datacenter = ?"
        params.append(datacenter)
    
    if device_name:
        query += " AND device_name LIKE ?"
        params.append(f"%{device_name}%")
    
    if device_type:
        query += " AND device_type = ?"
        params.append(device_type)
    
    if device_vendor:
        query += " AND device_vendor = ?"
        params.append(device_vendor)
    
    if dispatch_status:
        query += " AND dispatch_status = ?"
        params.append(dispatch_status)
    
    if resource_pool:
        query += " AND resource_pool = ?"
        params.append(resource_pool)
    
    if start_time:
        query += " AND occurrence_time >= ?"
        params.append(start_time)
    
    if end_time:
        query += " AND occurrence_time <= ?"
        params.append(end_time)
    
    query += " ORDER BY occurrence_time DESC LIMIT ? OFFSET ?"
    params.extend([limit, skip])
    
    cursor.execute(query, params)
    rows = cursor.fetchall()
    conn.close()
    
    alarms = []
    for row in rows:
        alarms.append(Alarm(
            id=row[0],
            global_alarm_id=row[1],
            alarm_status=row[2],
            alarm_title=row[3],
            alarm_type=row[4],
            alarm_level=row[5],
            occurrence_time=datetime.fromisoformat(row[6]),
            clear_time=datetime.fromisoformat(row[7]) if row[7] else None,
            region=row[8],
            datacenter=row[9],
            device_name=row[10],
            device_type=row[11],
            device_vendor=row[12],
            dispatch_status=row[13],
            resource_pool=row[14],
            alarm_content=row[15]
        ))
    
    return alarms

def get_alarms_count(
    global_alarm_id: Optional[str] = None,
    alarm_status: Optional[str] = None,
    alarm_title: Optional[str] = None,
    alarm_type: Optional[str] = None,
    alarm_level: Optional[str] = None,
    region: Optional[str] = None,
    datacenter: Optional[str] = None,
    device_name: Optional[str] = None,
    device_type: Optional[str] = None,
    device_vendor: Optional[str] = None,
    dispatch_status: Optional[str] = None,
    resource_pool: Optional[str] = None,
    start_time: Optional[str] = None,
    end_time: Optional[str] = None
):
    """获取符合条件的告警记录总数"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    query = "SELECT COUNT(*) FROM alarms WHERE 1=1"
    params = []
    
    if global_alarm_id:
        query += " AND global_alarm_id LIKE ?"
        params.append(f"%{global_alarm_id}%")
    
    if alarm_status:
        query += " AND alarm_status = ?"
        params.append(alarm_status)
    
    if alarm_title:
        query += " AND alarm_title LIKE ?"
        params.append(f"%{alarm_title}%")
    
    if alarm_type:
        query += " AND alarm_type = ?"
        params.append(alarm_type)
    
    if alarm_level:
        query += " AND alarm_level = ?"
        params.append(alarm_level)
    
    if region:
        query += " AND region = ?"
        params.append(region)
    
    if datacenter:
        query += " AND datacenter = ?"
        params.append(datacenter)
    
    if device_name:
        query += " AND device_name LIKE ?"
        params.append(f"%{device_name}%")
    
    if device_type:
        query += " AND device_type = ?"
        params.append(device_type)
    
    if device_vendor:
        query += " AND device_vendor = ?"
        params.append(device_vendor)
    
    if dispatch_status:
        query += " AND dispatch_status = ?"
        params.append(dispatch_status)
    
    if resource_pool:
        query += " AND resource_pool = ?"
        params.append(resource_pool)
    
    if start_time:
        query += " AND occurrence_time >= ?"
        params.append(start_time)
    
    if end_time:
        query += " AND occurrence_time <= ?"
        params.append(end_time)
    
    cursor.execute(query, params)
    count = cursor.fetchone()[0]
    conn.close()
    
    return count

def update_alarm(global_alarm_id: str, alarm_data: dict):
    """更新告警记录"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    # 添加更新时间
    alarm_data['updated_at'] = datetime.now().isoformat()
    
    set_clause = ", ".join([f"{key} = ?" for key in alarm_data.keys()])
    query = f"UPDATE alarms SET {set_clause} WHERE global_alarm_id = ?"
    params = list(alarm_data.values()) + [global_alarm_id]
    
    cursor.execute(query, params)
    conn.commit()
    affected_rows = cursor.rowcount
    conn.close()
    
    return affected_rows > 0

def get_alarm_statistics():
    """获取告警统计信息"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    # 获取总告警数
    cursor.execute("SELECT COUNT(*) FROM alarms")
    total_alarms = cursor.fetchone()[0]
    
    # 按告警级别统计
    cursor.execute("SELECT alarm_level, COUNT(*) FROM alarms GROUP BY alarm_level")
    alarms_by_level = dict(cursor.fetchall())
    
    # 按告警类型统计
    cursor.execute("SELECT alarm_type, COUNT(*) FROM alarms GROUP BY alarm_type")
    alarms_by_type = dict(cursor.fetchall())
    
    # 按告警状态统计
    cursor.execute("SELECT alarm_status, COUNT(*) FROM alarms GROUP BY alarm_status")
    alarms_by_status = dict(cursor.fetchall())
    
    # 按大区统计
    cursor.execute("SELECT region, COUNT(*) FROM alarms GROUP BY region")
    alarms_by_region = dict(cursor.fetchall())
    
    # 按机房统计
    cursor.execute("SELECT datacenter, COUNT(*) FROM alarms GROUP BY datacenter")
    alarms_by_datacenter = dict(cursor.fetchall())
    
    # 按派单状态统计
    cursor.execute("SELECT dispatch_status, COUNT(*) FROM alarms GROUP BY dispatch_status")
    alarms_by_dispatch_status = dict(cursor.fetchall())
    
    # 获取最新的10条告警
    cursor.execute("SELECT * FROM alarms ORDER BY occurrence_time DESC LIMIT 10")
    rows = cursor.fetchall()
    
    top_alarms = []
    for row in rows:
        top_alarms.append(Alarm(
            id=row[0],
            global_alarm_id=row[1],
            alarm_status=row[2],
            alarm_title=row[3],
            alarm_type=row[4],
            alarm_level=row[5],
            occurrence_time=datetime.fromisoformat(row[6]),
            clear_time=datetime.fromisoformat(row[7]) if row[7] else None,
            region=row[8],
            datacenter=row[9],
            device_name=row[10],
            device_type=row[11],
            device_vendor=row[12],
            dispatch_status=row[13],
            resource_pool=row[14],
            alarm_content=row[15]
        ))
    
    conn.close()
    
    return {
        "total_alarms": total_alarms,
        "alarms_by_level": alarms_by_level,
        "alarms_by_type": alarms_by_type,
        "alarms_by_status": alarms_by_status,
        "alarms_by_region": alarms_by_region,
        "alarms_by_datacenter": alarms_by_datacenter,
        "alarms_by_dispatch_status": alarms_by_dispatch_status,
        "top_alarms": top_alarms
    }
